alter PROC SP_DIE_BANK_RELEASE_INFO_REPORT

@PROCESS_STEP_ID VARCHAR(50),
@PRODUCT_ID VARCHAR(50),
@ASOFDATE VARCHAR(20),
@REPORT_TYPE VARCHAR(20),
@DEFAULT_PROMPT VARCHAR(20)
  
AS
  
DECLARE @LOT_NUMBER VARCHAR(50)
DECLARE @LOT_ID INT
DECLARE @TEMPLOT_ID INT
DECLARE @WAFER_ID INT
DECLARE @GOOD_DICE INT
DECLARE @YIELD FLOAT
DECLARE @BIN_1_BALANCE_QTY INT
DECLARE @BIN_2_BALANCE_QTY INT
DECLARE @BIN_3_BALANCE_QTY INT
DECLARE @BIN_4_BALANCE_QTY INT
DECLARE @BIN_5_BALANCE_QTY INT
DECLARE @BIN_6_BALANCE_QTY INT
DECLARE @BIN_7_BALANCE_QTY INT
DECLARE @BIN_8_BALANCE_QTY INT
DECLARE @BIN_9_BALANCE_QTY INT
DECLARE @BIN_10_BALANCE_QTY INT
DECLARE @BIN_11_BALANCE_QTY INT
DECLARE @BIN_12_BALANCE_QTY INT
DECLARE @BIN_13_BALANCE_QTY INT
DECLARE @BIN_14_BALANCE_QTY INT
DECLARE @BIN_15_BALANCE_QTY INT
DECLARE @PART_NUMBER VARCHAR(50)
DECLARE @TEMP_PROCESSSTEP_ID BIGINT
DECLARE @P_PARENT_PRODUCT_ID INT
DECLARE @BASE_PART_NUMBER VARCHAR(50)

DECLARE @MAINCURSOR CURSOR

DECLARE @RESULTS TABLE ( LOT_NUMBER VARCHAR(50),LOT_ID INT, WAFER_ID INT,BASE_PRODUCT VARCHAR(50), PART_NUMBER VARCHAR(50), PROCESSSTEP_ID BIGINT, 
			 BIN_1_BALANCE_QTY INT, BIN_2_BALANCE_QTY INT, BIN_3_BALANCE_QTY INT,
			 BIN_4_BALANCE_QTY INT, BIN_5_BALANCE_QTY INT, BIN_6_BALANCE_QTY INT, BIN_7_BALANCE_QTY INT, 
			 BIN_8_BALANCE_QTY INT, BIN_9_BALANCE_QTY INT, BIN_10_BALANCE_QTY INT, BIN_11_BALANCE_QTY INT, 
			 BIN_12_BALANCE_QTY INT,BIN_13_BALANCE_QTY INT, BIN_14_BALANCE_QTY INT, BIN_15_BALANCE_QTY INT,PRODUCT_ID INT )

BEGIN

	INSERT INTO @RESULTS ( LOT_NUMBER ,LOT_ID, WAFER_ID, BASE_PRODUCT, PART_NUMBER, PROCESSSTEP_ID, BIN_1_BALANCE_QTY, BIN_2_BALANCE_QTY, 
			 BIN_3_BALANCE_QTY, BIN_4_BALANCE_QTY, BIN_5_BALANCE_QTY, BIN_6_BALANCE_QTY, BIN_7_BALANCE_QTY, BIN_8_BALANCE_QTY, 
			 BIN_9_BALANCE_QTY, BIN_10_BALANCE_QTY, BIN_11_BALANCE_QTY, BIN_12_BALANCE_QTY,BIN_13_BALANCE_QTY, 
			 BIN_14_BALANCE_QTY, BIN_15_BALANCE_QTY , PRODUCT_ID)
			 SELECT LT.LOT_NUMBER, SBRT.LOT_ID, SBRT.WAFER_ID,
			 ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			 CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
			 END ), P.PART_NUMBER, LT.PROCESSSTEP_ID, ISNULL( BIN_1_BALANCE_QTY, BIN_1 ), ISNULL( BIN_2_BALANCE_QTY, BIN_2), ISNULL( BIN_3_BALANCE_QTY, BIN_3), 
 			 ISNULL( BIN_4_BALANCE_QTY, BIN_4), ISNULL( BIN_5_BALANCE_QTY, BIN_5), ISNULL( BIN_6_BALANCE_QTY, BIN_6), ISNULL( BIN_7_BALANCE_QTY, BIN_7),
			 ISNULL( BIN_8_BALANCE_QTY, BIN_8), ISNULL( BIN_9_BALANCE_QTY, BIN_9), ISNULL( BIN_10_BALANCE_QTY, BIN_10), ISNULL( BIN_11_BALANCE_QTY, BIN_11),  
			 ISNULL( BIN_12_BALANCE_QTY, BIN_12),ISNULL( BIN_13_BALANCE_QTY, BIN_13), ISNULL( BIN_14_BALANCE_QTY, BIN_14), ISNULL( BIN_15_BALANCE_QTY, BIN_15),P.P_PARENT_PRODUCT_ID
			 FROM SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT, PRODUCT P,PROCESS_STEP PS 
			 WHERE SBRT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.P_TYPE IS NULL

	IF( @PRODUCT_ID <> '0' )
	BEGIN
		DELETE FROM @RESULTS WHERE PRODUCT_ID <> @PRODUCT_ID
	END

	IF( @PROCESS_STEP_ID <> '0' )
	BEGIN
		DELETE FROM @RESULTS WHERE PROCESSSTEP_ID <> ( select PROCESSSTEP_ID from fn_get_process_flow_by_processstep_id(@PROCESS_STEP_ID) WHERE PS_TYPE = 'SORT' )
	END

	
	IF(@REPORT_TYPE = 'SUMMARY')
	BEGIN
		
		SELECT LOT_NUMBER,LOT_ID,'' WAFER_ID,BASE_PRODUCT,PART_NUMBER,PROCESSSTEP_ID,SUM(BIN_1_BALANCE_QTY) AS BIN_1_BALANCE_QTY, SUM(BIN_2_BALANCE_QTY) AS BIN_2_BALANCE_QTY, SUM(BIN_3_BALANCE_QTY) AS BIN_3_BALANCE_QTY, 
		 			 SUM(BIN_4_BALANCE_QTY) AS BIN_4_BALANCE_QTY, SUM(BIN_5_BALANCE_QTY) AS BIN_5_BALANCE_QTY, SUM(BIN_6_BALANCE_QTY) AS BIN_6_BALANCE_QTY, SUM(BIN_7_BALANCE_QTY) AS BIN_7_BALANCE_QTY, 
					 SUM(BIN_8_BALANCE_QTY) AS BIN_8_BALANCE_QTY, SUM(BIN_9_BALANCE_QTY) AS BIN_9_BALANCE_QTY, SUM(BIN_10_BALANCE_QTY) AS BIN_10_BALANCE_QTY, SUM(BIN_11_BALANCE_QTY) AS BIN_11_BALANCE_QTY,  
					 SUM(BIN_12_BALANCE_QTY) AS BIN_12_BALANCE_QTY,SUM(BIN_13_BALANCE_QTY) AS BIN_13_BALANCE_QTY, SUM(BIN_14_BALANCE_QTY) AS BIN_14_BALANCE_QTY, SUM(BIN_15_BALANCE_QTY) AS BIN_15_BALANCE_QTY 
		FROM @RESULTS GROUP BY LOT_NUMBER,LOT_ID,BASE_PRODUCT,PART_NUMBER,PROCESSSTEP_ID
			
	END
	ELSE
	BEGIN
		SELECT * FROM @RESULTS ORDER BY LOT_ID
				
	END

END